﻿using DB2WORD.Models;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace DB2WORD.Logics
{
    public static class Executor
    {
        public static Action<string>? MessageCallback;
        public static Result Run(string name, string? connectionString, List<TableInfo> tables)
        {
            try
            {
                MessageCallback?.Invoke("正在准备处理数据...");
                var ids = tables.Select(x => x.TableID).ToList();
                var where = "";
                if(ids.Any())
                {
                    where = $"WHERE t.object_id IN ({string.Join(",", ids)})";
                }
                using(var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using(var cmd = conn.CreateCommand())
                    {
                        MessageCallback?.Invoke("加载数据库字段信息...");
                        var columnsSql = string.Format(SQL_COLUMNS, where);
                        var tableColumns = Query(cmd, columnsSql);
                        var columns = tableColumns.AsList<ColumnInfo>();
                        foreach(var item in columns)
                        {
                            if(!string.IsNullOrWhiteSpace(item.Description))
                            {
                                var descs = item.Description.Split('|', 2);
                                if(!string.IsNullOrWhiteSpace(descs[0]))
                                {
                                    item.Label = descs[0];
                                }
                                else
                                {
                                    item.Label = null;
                                }
                                if(descs.Length > 1 && !string.IsNullOrWhiteSpace(descs[1]))
                                {
                                    item.Description = descs[1];
                                }
                                else
                                {
                                    item.Description = null;
                                }
                            }
                        }

                        MessageCallback?.Invoke("加载数据库索引信息...");
                        var indexesSql = string.Format(SQL_INDEXES, where);
                        var tableIndexes = Query(cmd, indexesSql);
                        var indexes = tableIndexes.AsList<IndexInfo>();

                        foreach(var table in tables)
                        {
                            table.Columns = columns.Where(x => x.TableID == table.TableID).ToList();
                            table.Indexes = indexes.Where(x => x.TableID == table.TableID).ToList();
                        }

                        List<GroupInfo> groupsNew;
                        var jsonFileName = $"{name}.json";
                        if(File.Exists(jsonFileName))
                        {
                            var tablesNew = new List<TableInfo>();
                            var jsonText = File.ReadAllText(jsonFileName);
                            groupsNew = JsonConvert.DeserializeObject<List<GroupInfo>>(jsonText);
                        }
                        else
                        {
                            groupsNew = new List<GroupInfo>();
                        }

                        foreach(var grp in groupsNew)
                        {
                            var newTables = new List<TableInfo>();
                            foreach(var e in grp.Tables)
                            {
                                var tbl = tables.FirstOrDefault(x => x.Name == e.Name);
                                if(tbl != null)
                                {
                                    e.TableID = tbl.TableID;
                                    newTables.Add(e);
                                    tables.Remove(tbl);
                                    e.Indexes = tbl.Indexes;
                                    if(tbl.Columns != null)
                                    {
                                        e.Columns = e.Columns ?? new List<ColumnInfo>();
                                        foreach(var col in tbl.Columns)
                                        {
                                            var ec = e.Columns.FirstOrDefault(x => x.Name == col.Name);
                                            if(ec != null)
                                            {
                                                ec.CanBeNull = col.CanBeNull;
                                                ec.Default = col.Default;
                                                ec.Type = col.Type;
                                                ec.IsPrimary = col.IsPrimary;
                                                ec.IsIdentity = col.IsIdentity;
                                                ec.Sort = col.Sort;
                                            }
                                            else
                                            {
                                                e.Columns.Add(col);
                                            }
                                        }
                                        e.Columns = e.Columns.OrderBy(x => x.Sort).ToList();
                                    }
                                }
                            }
                            grp.Tables = newTables.ToList();
                        }
                        if(tables.Any())
                        {
                            var lastGroup = groupsNew.FirstOrDefault(x => x.Name == "其他模块");
                            if(lastGroup == null)
                            {
                                lastGroup = new GroupInfo
                                {
                                    Name = "其他模块",
                                    Tables = new List<TableInfo>()
                                };
                                groupsNew.Add(lastGroup);
                            }
                            lastGroup.Tables = lastGroup.Tables.Concat(tables).OrderBy(x => x.Name).ToList();
                        }
                        groupsNew = groupsNew.Where(x => x.Tables.Count > 0).ToList();

                        var jsonText2 = JsonConvert.SerializeObject(groupsNew, JsonHelper.JsonSerializerSettings);
                        File.WriteAllText(jsonFileName, jsonText2, Encoding.UTF8);
                        MessageCallback?.Invoke("保存到文件：" + jsonFileName);
                        return Result.OK(groupsNew);
                    }
                }
            }
            catch(Exception ex)
            {
                MessageCallback?.Invoke("加载数据错误：" + ex.Message);
                return Result.Fail(ex.Message);
            }
        }

        private static DataTable Query(SqlCommand cmd, string sql, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            cmd.CommandText = sql;
            if(parameters != null)
            {
                foreach(var p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }

            var reader = cmd.ExecuteReader();
            dt.Load(reader, LoadOption.OverwriteChanges);
            return dt;
        }

        public static Result GetTableNames(string? connectionString)
        {
            try
            {
                using(var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using(var cmd = conn.CreateCommand())
                    {
                        var table = Query(cmd, SQL_TABLENAMES);
                        var list = table.AsList<TableInfo>();
                        foreach(var item in list)
                        {
                            if(!string.IsNullOrWhiteSpace(item.Description))
                            {
                                var descs = item.Description.Split('|', 2);
                                if(!string.IsNullOrWhiteSpace(descs[0]))
                                {
                                    item.Label = descs[0];
                                }
                                else
                                {
                                    item.Label = null;
                                }
                                if(descs.Length > 1 && !string.IsNullOrWhiteSpace(descs[1]))
                                {
                                    item.Description = descs[1];
                                }
                                else
                                {
                                    item.Description = null;
                                }
                            }
                        }
                        return Result.OK(list);
                    }
                }
            }
            catch(Exception ex)
            {
                return Result.Fail(ex.Message);
            }
        }

        public static Result GetTableDescriptionSql(TableInfo table)
        {
            var sql = new StringBuilder();
            sql.AppendLine($"--表 {table.Name} 创建描述脚本");
            var symbles = table.Name.Split('.');
            var tableDesc = table.Label?.Trim();
            var descValue = string.Empty;
            if(!string.IsNullOrWhiteSpace(tableDesc) && tableDesc != table.Name && tableDesc != symbles[1])
            {
                tableDesc.Replace("|", " ");
                descValue = tableDesc;
            }
            if(!string.IsNullOrWhiteSpace(table.Description))
            {
                descValue += "|" + table.Description;
            }
            if(!string.IsNullOrWhiteSpace(descValue))
            {
                sql.AppendLine(string.Format(SQL_TABLEDESC, descValue, symbles[0], symbles[1]));
                sql.AppendLine("GO");
            }

            foreach(var column in table.Columns)
            {
                var value = string.Empty;
                if(column.Label != column.Name)
                {
                    value += (column.Label ?? "").Trim();
                    value.Replace("|", " ");
                }
                if(!string.IsNullOrWhiteSpace(column.Description))
                {
                    value += "|" + column.Description.Trim();
                }
                value = value.Trim();
                if(!string.IsNullOrWhiteSpace(value))
                {
                    sql.AppendLine(string.Format(SQL_COLUMNDESC, value, symbles[0], symbles[1], column.Name));
                    sql.AppendLine("GO");
                }
            }
            var sqlStem = sql.ToString();
            return Result.OK(sqlStem);
        }

        public static Result GetDescriptionSql(List<GroupInfo> gruops)
        {
            var sql = new StringBuilder();
            sql.AppendLine(SQL_DROP_DESCRIPTION);
            sql.AppendLine(SQL_CREATE_DESCRIPTION);

            foreach(var gruop in gruops)
            {
                sql.AppendLine($"--模块 {gruop.Name} 创建描述脚本");
                foreach(var table in gruop.Tables)
                {
                    var sqlTbl = GetTableDescriptionSql(table).As<string>();
                    sql.AppendLine(sqlTbl);
                }
            }
            //sql.AppendLine(SQL_DROP_DESCRIPTION);
            var sqlStem = sql.ToString();
            return Result.OK(sqlStem);
        }

        private const string SQL_TABLENAMES = @"
            SELECT t.object_id AS TableID, 
	            s.name+'.'+t.name AS Name ,
	            t.name AS Label,
	            d.value AS Description
            FROM sys.tables t
            INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
            LEFT JOIN sys.extended_properties d ON d.major_id = t.object_id AND d.minor_id = 0
            ORDER BY 2";


        private const string SQL_COLUMNS = @"
            SELECT  t.object_id AS TableID ,
                c.name AS Name ,
				c.column_id AS Sort,
				UPPER(ISNULL(TYPE_NAME(c.system_type_id), y.name)) AS Type,
				c.is_identity AS IsIdentity,
				0 AS IsPrimary,
				c.is_nullable AS CanBeNull,
				OBJECT_DEFINITION(c.default_object_id) AS [Default],
                d.value AS Description,
				COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen') AS max_length,
				c.precision, c.scale,
                c.name AS Label
		    INTO #temptable
            FROM sys.tables t
		    INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
            INNER JOIN sys.columns c ON c.object_id = t.object_id
		    INNER JOIN sys.types y ON c.user_type_id=y.user_type_id	
            LEFT JOIN sys.extended_properties d ON d.major_id = t.object_id AND d.minor_id = c.column_id
            {0}
		    ORDER BY t.object_id,c.column_id 

		    UPDATE t SET t.IsPrimary=1
		    FROM #temptable t
		    INNER JOIN sys.key_constraints k ON k.parent_object_id = t.TableID AND k.type='PK'
		    INNER JOIN sys.index_columns c ON c.object_id = k.parent_object_id AND c.index_id = k.unique_index_id AND t.Sort=c.column_id

		    UPDATE #temptable 
		    SET Type=Type+COALESCE('(' +  CASE WHEN max_length<0 THEN 'MAX' ELSE CAST(max_length AS  VARCHAR) END + ')', '') 
		    WHERE max_length IS NOT NULL
		
		    UPDATE #temptable 
		    SET Type=Type+COALESCE('(' +  CAST(precision AS  VARCHAR) +','+  CAST(scale AS  VARCHAR) + ')', '') 
		    WHERE Type IN ('NUMERIC','DECIMAL')

		    SELECT TableID, Name, Type, IsPrimary, IsIdentity,
                CanBeNull, [Default], Description, Label, Sort
		    FROM #temptable
		    ORDER BY TableID,Sort

		    DROP TABLE #temptable";


        private const string SQL_INDEXES = @"
        SELECT t.object_id AS TableID, i.name AS Name,i.type,i.is_unique,i.is_primary_key,i.index_id AS Sort,
	        c.name+' '+CASE ic.is_descending_key WHEN 1 THEN 'DESC' ELSE 'ASC' END  AS col
        INTO #indexinfos
        FROM sys.indexes i
        INNER JOIN sys.tables t ON i.object_id=t.object_id
        INNER JOIN sys.index_columns ic ON ic.index_id=i.index_id AND ic.object_id = t.object_id
        INNER JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = ic.column_id
        {0}
        ORDER BY t.name,i.index_id,ic.key_ordinal

        SELECT t.TableID,
			ROW_NUMBER()OVER(PARTITION BY t.TableID ORDER BY t.Sort) AS Sort , t.Name,
			(CASE WHEN t.is_primary_key=1 THEN '主键 ' else '' END)
			+(CASE WHEN t.is_unique=1 THEN '唯一 ' else '' END)
			+(CASE WHEN t.type=1 THEN '聚集' else '非聚集' END) AS Description, 
			dbo.FN_StrJoin(t.col) AS Columns 
        FROM #indexinfos t
        GROUP BY t.TableID,t.Sort, t.Name, t.type,t.is_unique,t.is_primary_key
        ORDER BY t.TableID,t.Sort

        DROP TABLE #indexinfos";

        private const string SQL_DROP_DESCRIPTION = $@"
--删除存储 SP_AddOrUpdateDescription
IF EXISTS(SELECT 1 FROM sys.procedures WHERE name='SP_AddOrUpdateDescription')
	DROP PROC SP_AddOrUpdateDescription
GO";

        private const string SQL_CREATE_DESCRIPTION = $@"
--创建存储 SP_AddOrUpdateDescription
CREATE PROC SP_AddOrUpdateDescription
	@value SQL_VARIANT,
	@schameName VARCHAR(64),
	@tableName VARCHAR(64),
	@columnName VARCHAR(64)=null
AS 

	IF ISNULL(LTRIM(RTRIM(@columnName)),'')='' 
	BEGIN		
		IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE OBJECT_ID(@schameName+'.'+@tableName) = major_id AND minor_id=0)
		BEGIN
			EXEC sys.sp_dropextendedproperty @name = N'MS_Description',       -- sysname
			                                 @level0type =  N'SCHEMA',   -- varchar(128)
			                                 @level0name = @schameName, -- sysname
			                                 @level1type = N'TABLE',   -- varchar(128)
			                                 @level1name = @tableName -- sysname
		END
		EXEC sys.sp_addextendedproperty @name = N'MS_Description',       -- sysname
		                                @value = @value,      -- sql_variant
			                            @level0type =  N'SCHEMA',   -- varchar(128)
			                            @level0name = @schameName, -- sysname
			                            @level1type = N'TABLE',   -- varchar(128)
			                            @level1name = @tableName -- sysname
	END
	ELSE
    BEGIN
		IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE OBJECT_ID(@schameName+'.'+@tableName) = major_id AND col_name(major_id, minor_id)=@columnName)
		BEGIN
			EXEC sys.sp_dropextendedproperty @name = N'MS_Description',       -- sysname
			                                 @level0type =  N'SCHEMA',   -- varchar(128)
			                                 @level0name = @schameName, -- sysname
			                                 @level1type = N'TABLE',   -- varchar(128)
			                                 @level1name = @tableName, -- sysname
											 @level2type = N'COLUMN',
											 @level2name = @columnName
		END
		EXEC sys.sp_addextendedproperty @name = N'MS_Description',       -- sysname
		                                @value = @value,      -- sql_variant
			                            @level0type =  N'SCHEMA',   -- varchar(128)
			                            @level0name = @schameName, -- sysname
			                            @level1type = N'TABLE',   -- varchar(128)
			                            @level1name = @tableName, -- sysname
										@level2type = N'COLUMN',
										@level2name = @columnName

	END
GO
";

        private const string SQL_TABLEDESC = @"EXEC SP_AddOrUpdateDescription '{0}','{1}','{2}'";
        private const string SQL_COLUMNDESC = @"EXEC SP_AddOrUpdateDescription '{0}','{1}','{2}','{3}'";
    }
}
